import 'dart:ffi';

import 'package:landlore/consts.dart';
import 'package:landlore/model/orderVO.dart';
import 'package:landlore/model/reviewVO.dart';
import 'package:landlore/model/roomCheckInOut.dart';
import 'package:landlore/model/roomCount.dart';
import 'package:landlore/model/roomInfo.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

class TableName {
  static const String ROOM_INFO = 'room_info';
  static const String ORDER = 'orders';
  static const String ROOM_COUNT = 'room_count';
  static const String ROOM_CHECK_IN_OUT = 'room_check_in_out';
}

class DatabaseHelper {
  static const _databaseName = "landlord.db";
  static const _databaseVersion = 5;

  // 声明一个私有构造函数，避免直接实例化该类
  DatabaseHelper._privateConstructor();

  static final DatabaseHelper instance = DatabaseHelper._privateConstructor();

  // 数据库对象
  static Database? _database;

  Future<Database> get database async {
    if (_database != null) {
      return _database!;
    }
    _database = await _initDatabase();
    return _database!;
  }

  // 初始化数据库
  Future<Database> _initDatabase() async {
    String path = join(await getDatabasesPath(), _databaseName);
    print('path:$path');

    // // 删除数据库文件
    // await File(path).delete();
    // print('Database deleted 删库!!!!!');

    print('DB初始化开始,数据库版本设置为:$_databaseVersion');
    // String path = join(await getDatabasesPath(), _databaseName);
    return await openDatabase(
      path,
      version: _databaseVersion,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
    );
  }

  // 创建表
  Future<void> _onCreate(Database db, int version) async {
    await db.execute('''
        CREATE TABLE room_info (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        roomNum TEXT,
        contact TEXT,
        address TEXT,
        phone TEXT,
        rent REAL,
        otherCharge REAL,
        electricityCharge REAL,
        waterCharge REAL,
        state INTEGER );
    );
    ''');
    await db.execute('''
    CREATE TABLE orders (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        roomId INTEGER,
        roomNum TEXT,
        createDate INTEGER,
        createDateYear INTEGER,
        createDateMonth INTEGER,
        rent REAL,
        otherCharge REAL,
        electricityCharge REAL,
        electricityUnitPrice REAL,
        electricityAmount INTEGER,
        electricityAmountLastMonth INTEGER,
        electricityAmountResult INTEGER,
        waterCharge REAL,
        waterUnitPrice REAL,
        waterAmount INTEGER,
        waterAmountLastMonth INTEGER,
        waterAmountResult INTEGER,
        eleImage TEXT,
        waterImage TEXT,
        state INTEGER);
    ''');
    await db.execute('''
    CREATE TABLE room_count (
        createDate INTEGER PRIMARY KEY,
        createDateYear INTEGER,
        createDateMonth INTEGER,
        fulRentRate REAL);
    ''');
    print('roomInfo表创建完毕');
    print('orders表创建完毕');
    print('roomCount表创建完毕');

    //执行所有更新操作
    _upgradeDataBaseV1(db);
    _upgradeDataBaseV2(db);
    _upgradeDataBaseV3(db);
    _upgradeDataBaseV4(db);
  }

  _onUpgrade(db, oldVersion, newVersion) async {
    print('更新检测,oldVersion:$oldVersion,newVersion:$newVersion');
    if (oldVersion < 2) {
      await _upgradeDataBaseV1(db);
    }
    if (oldVersion < 3) {
      await _upgradeDataBaseV2(db);
    }
    if (oldVersion < 4) {
      await _upgradeDataBaseV3(db);
    }
    if (oldVersion < 5) {
      await _upgradeDataBaseV4(db);
    }
  }

  _upgradeDataBaseV1(Database db) async {
    await db.execute('''
        ALTER TABLE orders
        ADD COLUMN checkState INTEGER DEFAULT 0;
      ''');
    print('数据库版本:2,更新完成');
  }

  _upgradeDataBaseV2(Database db) async {
    await db.execute('''
        ALTER TABLE orders
        ADD COLUMN contact TEXT;
      ''');
    print('数据库版本:3,更新完成');
  }

  _upgradeDataBaseV3(Database db) async {
    await db.execute('''
          CREATE TABLE room_check_in_out (
            id                INTEGER PRIMARY KEY AUTOINCREMENT
            UNIQUE,
            roomId            INTEGER NOT NULL,
            contact           TEXT,
            address           TEXT,
            phone             TEXT,
            deposit           REAL    DEFAULT (0),
            rent              REAL,
            otherCharge       REAL,
            electricityCharge REAL,
            waterCharge       REAL,
            createDate        INTEGER,
            extend            TEXT,
            state             INTEGER
            );
      ''');
    print('room_check_in_out表创建完毕');
  }

  _upgradeDataBaseV4(Database db) async {
    await db.execute('''
        ALTER TABLE room_check_in_out
        ADD COLUMN eleImage TEXT;
      ''');
    print('room_check_in_out表,更新eleImage');

    await db.execute('''
        ALTER TABLE room_check_in_out
        ADD COLUMN waterImage TEXT ;
      ''');
    print('room_check_in_out表,更新waterImage');

    await db.execute('''
        ALTER TABLE room_check_in_out
        ADD COLUMN electricityAmount INTEGER DEFAULT (0);
      ''');
    print('room_check_in_out表,更新electricityAmount');

    await db.execute('''
        ALTER TABLE room_check_in_out
        ADD COLUMN electricityAmountLastMonth INTEGER DEFAULT (0);
      ''');
    print('room_check_in_out表,更新electricityAmountLastMonth');

    await db.execute('''
        ALTER TABLE room_check_in_out
        ADD COLUMN waterAmount INTEGER DEFAULT (0);
      ''');
    print('room_check_in_out表,更新waterAmount');

    await db.execute('''
        ALTER TABLE room_check_in_out
        ADD COLUMN waterAmountLastMonth INTEGER DEFAULT (0);
      ''');
    print('room_check_in_out表,更新waterAmountLastMonth');


  }


  // _upgradeDataBaseV3(Database db) async {
  //   await db.execute('''
  //       update orders set contact = t2.contact
  //       from room_info t2
  //       where roomId=t2.id;
  //     ''');
  //   print('数据库版本:4,更新完成');
  // }

  // 插入数据
  insertData(Map<String, dynamic> data, String tableName) async {
    Database db = await instance.database;
    int result = await db.insert(tableName, data);
    print('新增结果:$result');
  }

  // 通用的更新数据函数
  updateData(Map<String, dynamic> data, String tableName,
      {required String primaryKey, required int primaryKeyValue}) async {
    final db = await database;
    int result = await db.update(
      tableName,
      data,
      where: '$primaryKey = ?',
      whereArgs: [primaryKeyValue],
    );
    print('更新结果:$result');
  }

  // 通用的删除数据函数
  deleteData(String tableName,
      {required String primaryKey, required int primaryKeyValue}) async {
    final db = await database;
    int result = await db.delete(
      tableName,
      where: '$primaryKey = ?',
      whereArgs: [primaryKeyValue],
    );
    print('删除结果:$result');
  }

  // 查询数据
  Future<List<Map<String, dynamic>>> queryAllRows(String tableName) async {
    Database db = await instance.database;
    return await db.query(tableName);
  }

  Future<List<RoomInfo>> getRoomInfoAll() async {
    Database db = await instance.database;
    List queryMaps = await db
        .rawQuery('select * from ${TableName.ROOM_INFO} order by roomNum asc');
    List<RoomInfo> result = [];
    for (var map in queryMaps) {
      result.add(RoomInfo.fromMap(map));
    }
    return result;
  }

  switchRoomState(int roomId, bool state) async {
    Database db = await instance.database;
    int result = await db.update(
      TableName.ROOM_INFO,
      {'state': state ? 1 : 0},
      where: 'id = ?',
      whereArgs: [roomId],
    );
    print('更新结果:$result');
  }

  switchOrderState(int orderId, int state) async {
    Database db = await instance.database;
    int result = await db.update(
      TableName.ORDER,
      {'state': state},
      where: 'id = ?',
      whereArgs: [orderId],
    );
    print('更新结果:$result');
  }

  Future<RoomInfo> getRoomInfoById(int roomId) async {
    Database db = await instance.database;
    List queryMaps = await db.rawQuery(
        'select * from ${TableName.ROOM_INFO} where id = ?', [roomId]);
    List<RoomInfo> result = [];
    for (var map in queryMaps) {
      result.add(RoomInfo.fromMap(map));
    }
    return result[0];
  }

  Future<int> getRoomCount() async {
    Database db = await instance.database;
    List queryMaps = await db
        .rawQuery('select count(*) as roomCount from ${TableName.ROOM_INFO}');
    int result = 0;
    if (queryMaps.isNotEmpty) {
      for (var map in queryMaps) {
        result = map['roomCount'];
      }
    }
    return result;
  }

  Future<int> getOrderCount(DateTime createDate) async {
    Database db = await instance.database;
    List queryMaps = await db.rawQuery(
        'select count(1) as orderCount from ${TableName
            .ORDER} where createDate = ? and state = ${OrderState.BILL_OUT}',
        [createDate.millisecondsSinceEpoch]);
    int result = 0;
    if (queryMaps.isNotEmpty) {
      for (var map in queryMaps) {
        result = map['orderCount'];
      }
    }
    return result;
  }

  ///检测房屋代号是否已经存在,true:存在,false:不存在
  Future<bool> checkRoomNumExist(String roomNum, int? id) async {
    Database db = await instance.database;
    List queryMaps = [];
    if (id == null) {
      queryMaps = await db.rawQuery(
          'select * from ${TableName.ROOM_INFO} where roomNum = ?  limit 1',
          [roomNum]);
    } else {
      queryMaps = await db.rawQuery(
          'select * from ${TableName
              .ROOM_INFO} where roomNum = ? and id<>? limit 1',
          [roomNum, id]);
    }

    List<RoomInfo> result = [];
    for (var map in queryMaps) {
      result.add(RoomInfo.fromMap(map));
    }
    if (result.isNotEmpty) {
      return true;
    } else {
      return false;
    }
  }

  ///获取首页所有房产信息
  ///1.房产处于启动状态
  ///2.获取订单状态,若未抄表,这需要该置零的数据置零
  ///3.关于用户信息修改,因为有可能查看过往信息,所以用户信息应该以order表为准,若order信息为空,则显示roomInfo信息
  Future<List<OrderVO>> getHomePageRoom(int year, int month,
      int titleRankButtonFlag) async {
    Database db = await instance.database;
    int selectedDate = DateTime(year, month).millisecondsSinceEpoch;
    StringBuffer sql = StringBuffer('''
    SELECT t1.id AS roomId,
       t2.id,
       CASE WHEN t2.roomNum ISNULL THEN t1.roomNum ELSE t2.roomNum END AS roomNum,
       CASE WHEN t2.contact ISNULL THEN t1.contact ELSE t2.contact END AS contact,
       ? as createDate,
       CASE WHEN t2.electricityAmount ISNULL THEN 0 ELSE t2.electricityAmount END AS electricityAmount,
       CASE WHEN t2.electricityAmountLastMonth ISNULL THEN 0 ELSE t2.electricityAmountLastMonth END AS electricityAmountLastMonth,
       CASE WHEN t2.electricityAmountResult ISNULL THEN 0 ELSE t2.electricityAmountResult END AS electricityAmountResult,
       CASE WHEN t2.waterAmount ISNULL THEN 0 ELSE t2.waterAmount END AS waterAmount,
       CASE WHEN t2.waterAmountLastMonth ISNULL THEN 0 ELSE t2.waterAmountLastMonth END AS waterAmountLastMonth,
       CASE WHEN t2.waterAmountResult ISNULL THEN 0 ELSE t2.waterAmountResult END AS waterAmountResult,
       CASE WHEN t2.state ISNULL THEN 0 ELSE t2.state END AS state,
       CASE WHEN t2.checkState ISNULL THEN 0 ELSE t2.checkState END AS checkState,
       t2.rent,t2.otherCharge,t2.electricityCharge,t2.electricityUnitPrice,t2.waterCharge,t2.waterUnitPrice,t2.eleImage,t2.waterImage
       FROM room_info t1
       LEFT JOIN
       orders t2 ON t1.id = t2.roomId AND 
                    t2.createDate = ?
       WHERE t1.state = 1
       
    ''');
    if(titleRankButtonFlag == 0){
      sql.write('ORDER BY t1.roomNum ASC ');
    }else if(titleRankButtonFlag == 1){
      sql.write('ORDER BY t2.checkState DESC,t1.roomNum ASC ');
    }else{
      sql.write('ORDER BY t2.checkState ASC,t1.roomNum ASC ');
    }
    List queryMaps = await db.rawQuery(sql.toString(), [selectedDate, selectedDate]);
    List<OrderVO> result = [];
    for (var map in queryMaps) {
      OrderVO temp = OrderVO.fromMap(map);
      //获取上月 用电 用水量
      if (temp.createDate == null) {
        result.add(temp);
      } else {
        OrderVO lastMonthOrder = await getLastMonthDataByRoomId_DateTime(
            temp.roomId!, temp.createDate!);

        int tempEle = lastMonthOrder.electricityAmountResult ?? 0;
        int tempWater = lastMonthOrder.waterAmountResult ?? 0;

        temp.electricityAmountLastMonthCalculated = tempEle;
        temp.waterAmountLastMonthCalculated = tempWater;
        result.add(temp);
      }
    }
    return result;
  }

  Future<List<OrderVO>> getOrder() async {
    Database db = await instance.database;
    List queryMaps = await db.rawQuery('''
    SELECT * from ${TableName.ORDER}
    ''');
    List<OrderVO> result = [];
    for (var map in queryMaps) {
      result.add(OrderVO.fromMap(map));
    }
    return result;
  }

  /***
   * 清除无效缓存图片:Imgtype  0:电表照片 1:水表照片
   */
  clearCacheImg(int orderId, int imgType) async {
    if (imgType == 0) {
      final db = await database;
      int result = await db.update(
        TableName.ORDER,
        {'eleImage': null},
        where: 'id = ?',
        whereArgs: [orderId],
      );
    } else {
      final db = await database;
      int result = await db.update(
        TableName.ORDER,
        {'waterImage': null},
        where: 'id = ?',
        whereArgs: [orderId],
      );
    }
    //更新状态

  }


  Future<List<OrderVO>> getOrderByRoomId(int roomId) async {
    Database db = await instance.database;
    List queryMaps = await db.rawQuery('''
    SELECT
      t1.id,t1.roomId,
      t2.roomNum,t2.contact,
      t1.createDate,t1.rent,t1.otherCharge,
      t1.electricityCharge,t1.electricityUnitPrice,t1.electricityAmount,t1.electricityAmountLastMonth,t1.electricityAmountResult,
      t1.waterCharge,t1.waterUnitPrice,t1.waterAmount,t1.waterAmountLastMonth,t1.waterAmountResult,
      t1.eleImage,t1.waterImage,t1.state
    from ${TableName.ORDER} t1
    left join ${TableName.ROOM_INFO} t2 on t1.roomId=t2.id
    WHERE t1.roomId = ?
    ORDER BY t1.createDate desc;
    ''', [roomId]);
    List<OrderVO> result = [];
    for (var map in queryMaps) {
      result.add(OrderVO.fromMap(map));
    }
    return result;
  }

  Future<List<RoomCheckInOut>> getCheckInOutDataByRoomId(int roomId) async {
    Database db = await instance.database;
    List queryMaps = await db.rawQuery('''
    SELECT
      *
    from ${TableName.ROOM_CHECK_IN_OUT} t1
    WHERE t1.roomId = ?
    ORDER BY t1.createDate desc;
    ''', [roomId]);
    List<RoomCheckInOut> result = [];
    for (var map in queryMaps) {
      result.add(RoomCheckInOut.fromMap(map));
    }
    return result;
  }

  Future<List<OrderVO>> getOrderFroRecordScreen(int year, int month,
      String keyword) async {
    Database db = await instance.database;
    int selectedDate;
    if (month == 0) {
      selectedDate = 0;
    } else {
      selectedDate = DateTime(year, month).millisecondsSinceEpoch;
    }

    //两种查询,1.总是有结果  2.只有产生order才能查询

    // StringBuffer sql = StringBuffer('''
    // SELECT t1.id AS roomId,
    //    t2.id,
    //    t1.roomNum,
    //    t2.contact,
    //    ? as createDate,
    //    CASE WHEN t2.electricityAmount ISNULL THEN 0 ELSE t2.electricityAmount END AS electricityAmount,
    //    CASE WHEN t2.electricityAmountLastMonth ISNULL THEN 0 ELSE t2.electricityAmountLastMonth END AS electricityAmountLastMonth,
    //    CASE WHEN t2.electricityAmountResult ISNULL THEN 0 ELSE t2.electricityAmountResult END AS electricityAmountResult,
    //    CASE WHEN t2.waterAmount ISNULL THEN 0 ELSE t2.waterAmount END AS waterAmount,
    //    CASE WHEN t2.waterAmountLastMonth ISNULL THEN 0 ELSE t2.waterAmountLastMonth END AS waterAmountLastMonth,
    //    CASE WHEN t2.waterAmountResult ISNULL THEN 0 ELSE t2.waterAmountResult END AS waterAmountResult,
    //    CASE WHEN t2.state ISNULL THEN 0 ELSE t2.state END AS state,
    //    t2.rent,
    //    CASE WHEN t2.otherCharge ISNULL THEN 0.0 ELSE t2.otherCharge END AS otherCharge,
    //    t2.electricityCharge,t2.electricityUnitPrice,t2.waterCharge,t2.waterUnitPrice,t2.eleImage,t2.waterImage
    //    FROM room_info t1
    //    LEFT JOIN
    //    orders t2 ON t1.id = t2.roomId AND
    //                 t2.createDate = ?
    //
    // ''');
    // if(keyword.isNotEmpty){
    //   sql.write(" WHERE t1.roomNum like ? ");
    // }
    // sql.write(" ORDER BY t1.roomNum ASC; ");

    StringBuffer sql = StringBuffer('''
    SELECT t2.roomId ,
       t2.id,
       t2.roomNum,
       t2.contact,
       t2.createDate,
       CASE WHEN t2.electricityAmount ISNULL THEN 0 ELSE t2.electricityAmount END AS electricityAmount,
       CASE WHEN t2.electricityAmountLastMonth ISNULL THEN 0 ELSE t2.electricityAmountLastMonth END AS electricityAmountLastMonth,
       CASE WHEN t2.electricityAmountResult ISNULL THEN 0 ELSE t2.electricityAmountResult END AS electricityAmountResult,
       CASE WHEN t2.waterAmount ISNULL THEN 0 ELSE t2.waterAmount END AS waterAmount,
       CASE WHEN t2.waterAmountLastMonth ISNULL THEN 0 ELSE t2.waterAmountLastMonth END AS waterAmountLastMonth,
       CASE WHEN t2.waterAmountResult ISNULL THEN 0 ELSE t2.waterAmountResult END AS waterAmountResult,
       CASE WHEN t2.state ISNULL THEN 0 ELSE t2.state END AS state,
       t2.rent,
       CASE WHEN t2.otherCharge ISNULL THEN 0.0 ELSE t2.otherCharge END AS otherCharge,
       t2.electricityCharge,t2.electricityUnitPrice,t2.waterCharge,t2.waterUnitPrice,t2.eleImage,t2.waterImage
       FROM orders t2
       WHERE 1=1 
    ''');
    List paramater = [];
    if (selectedDate != 0) {
      sql.write(" AND t2.createDate = ? ");
      paramater.add(selectedDate);
    }
    if (keyword.isNotEmpty) {
      sql.write(" AND t2.roomNum like ? ");
      paramater.add('%$keyword%');
    }
    sql.write(" ORDER BY t2.roomNum ASC,t2.createDate DESC; ");

    List queryMaps = await db.rawQuery(sql.toString(), paramater);
    List<OrderVO> result = [];
    for (var map in queryMaps) {
      result.add(OrderVO.fromMap(map));
    }
    return result;
  }

  /// 获取上月抄表数据.只获取用电量,用水量
  ///
  /// 根据roomId和目标时间,查出所有order
  /// 根据createDate倒序排列
  /// 获取创建日期 小于 目标值的数据.
  /// 此条件下,日期最大的一条数据,就是上月的数据
  /// 特殊情况:结果条数为0,暂时还不存在上月数据.此时返回上月用量为0
  ///
  Future<OrderVO> getLastMonthDataByRoomId_DateTime(int roomId,
      DateTime createDate) async {
    Database db = await instance.database;
    //获取创建日期小于目标值的数据.
    //此条件下,日期最大的一条数据,就是上月的数据
    //特殊情况:
    // 结果条数为0,暂时还不存在上月数据.此时返回0
    List<Map<String, dynamic>> queryMaps = await db.rawQuery('''
     select * from ${TableName.ORDER} 
     where roomId = ? and createDate<? 
     order by createDate desc
     limit 1
    ''', [roomId, createDate.millisecondsSinceEpoch]);

    List<OrderVO> result = [];
    for (var map in queryMaps) {
      result.add(OrderVO.fromMap(map));
    }
    if (result.isEmpty) {
      //结果条数为0,暂时还不存在上月数据.此时返回0
      return OrderVO.lastMonthData(null,0, 0, 0, 0, 0, 0);
    } else {
      //正常情况,1条记录
      return OrderVO.lastMonthData(
          result[0].createDate,
          result[0].electricityAmount,
          result[0].electricityAmountLastMonth,
          result[0].electricityAmountResult,
          result[0].waterAmount,
          result[0].waterAmountLastMonth,
          result[0].waterAmountResult);
    }
  }

  //获取出单页面的订单信息,
  //房屋基本信息从roomInfo获取,以防止用户要修改room基本信息
  getOrderByIdForBillOut(int id) async {
    Database db = await instance.database;
    List<Map<String, dynamic>> queryMaps = await db.rawQuery('''
      select t1.id,t1.roomId,t1.createDate,t1.createDateYear,t1.createDateMonth,
      t1.electricityCharge,t1.electricityAmount,t1.electricityAmountLastMonth,t1.electricityAmountResult,
      t1.waterCharge,t1.waterAmount,t1.waterAmountLastMonth,t1.waterAmountResult,
      t1.eleImage,t1.waterImage,t1.state,
      t2.roomNum,t2.contact,t2.rent,t2.otherCharge,t2.electricityCharge as electricityUnitPrice ,t2.waterCharge as waterUnitPrice
      from ${TableName.ORDER} t1
      left join ${TableName.ROOM_INFO} t2 on t1.roomId=t2.id
      where t1.id = ? 
    ''', [id]);

    List<OrderVO> result = [];
    for (var map in queryMaps) {
      result.add(OrderVO.fromMap(map));
    }
    return result[0];
  }

  ///创建订单
  createOrder(int? orderId,
      int roomId,
      String roomNum,
      int year,
      int month,
      int electricityAmount,
      int electricityAmountLastMonth,
      int waterAmount,
      int waterAmountLastMonth,
      String? eleImage,
      String? waterImage,
      int checkState) async {
    //若上月抄表数均为0,则说明为第一次使用,把上月数设置为当前抄表数
    if (electricityAmountLastMonth == 0 && waterAmountLastMonth == 0) {
      electricityAmountLastMonth = electricityAmount;
      waterAmountLastMonth = waterAmount;
    }

    int tempEle = electricityAmount.abs() - electricityAmountLastMonth;
    int tempWater = waterAmount - waterAmountLastMonth;

    //需要处理负数的情况
    if (tempEle < 0) {
      if (Consts.NEGATIVE_BILL_CALCULATE_WAY) {
        tempEle = 0;
      } else {
        tempEle = Consts().calculateBillFulSituation(
            electricityAmountLastMonth, electricityAmount);
      }
    }

    if (tempWater < 0) {
      if (Consts.NEGATIVE_BILL_CALCULATE_WAY) {
        tempWater = 0;
      } else {
        tempWater = Consts()
            .calculateBillFulSituation(waterAmountLastMonth, waterAmount);
      }
    }

    //整理时间参数
    DateTime createDate = DateTime(year, month);
    //获取房屋最新资料
    RoomInfo roomInfo = await getRoomInfoById(roomId);

    OrderVO insertVO = OrderVO(
        orderId,
        roomId,
        roomInfo.roomNum,
        roomInfo.contact,
        createDate,
        year,
        month,
        roomInfo.rent,
        roomInfo.otherCharge,
        (roomInfo.electricityCharge! * tempEle),
        roomInfo.electricityCharge,
        electricityAmount,
        electricityAmountLastMonth,
        tempEle,
        (roomInfo.waterCharge! * tempWater),
        roomInfo.waterCharge,
        waterAmount,
        waterAmountLastMonth,
        tempWater,
        eleImage,
        waterImage,
        OrderState.FINISHED,
        checkState);

    if (orderId == null) {
      insertData(insertVO.toMap(), TableName.ORDER);
    } else {
      updateData(insertVO.toMap(), TableName.ORDER,
          primaryKey: 'id', primaryKeyValue: orderId);
    }

    //===================统计满租率==已经转移到出单阶段==================
    // int roomCount = await getRoomCount();
    // int orderCount = await getOrderCount(createDate);
    // RoomCount temp = RoomCount(createDate, year, month, orderCount / roomCount);
    // // 检查记录是否已存在
    // Database db = await instance.database;
    // final existingRecord = await db.query(TableName.ROOM_COUNT,
    //     where: 'createDate = ?',
    //     whereArgs: [createDate.millisecondsSinceEpoch]);
    // if (existingRecord.isNotEmpty) {
    //   // 记录已存在，执行更新操作
    //   await updateData(temp.toMap(), TableName.ROOM_COUNT,
    //       primaryKey: 'createDate',
    //       primaryKeyValue: createDate.millisecondsSinceEpoch);
    // } else {
    //   // 记录不存在，执行插入操作
    //   await insertData(temp.toMap(), TableName.ROOM_COUNT);
    // }
  }

  ///修改order表CheckState状态,1:已付款  0:未付款
  changeCheckState(int orderId, int checkState) async {
    if (checkState == 0) {
      checkState = 1;
    } else {
      checkState = 0;
    }
    //更新状态
    final db = await database;
    int result = await db.update(
      TableName.ORDER,
      {'checkState': checkState},
      where: 'id = ?',
      whereArgs: [orderId],
    );

    // .updateData(orderOV.toMap(), TableName.ORDER,
    // primaryKey: 'id', primaryKeyValue: orderOV.id!);
  }


  billOut(OrderVO orderOV) async {
    //出单,更新状态
    await DatabaseHelper.instance.updateData(orderOV.toMap(), TableName.ORDER,
        primaryKey: 'id', primaryKeyValue: orderOV.id!);
    //===================统计满租率====================
    int roomCount = await getRoomCount();
    int orderCount = await getOrderCount(orderOV.createDate!);
    print(orderOV);
    RoomCount temp = RoomCount(orderOV.createDate!, orderOV.createDateYear!,
        orderOV.createDateMonth!, orderCount / roomCount);
    // 检查记录是否已存在
    Database db = await instance.database;
    final existingRecord = await db.query(TableName.ROOM_COUNT,
        where: 'createDate = ?',
        whereArgs: [orderOV.createDate!.millisecondsSinceEpoch]);
    if (existingRecord.isNotEmpty) {
      // 记录已存在，执行更新操作
      await updateData(temp.toMap(), TableName.ROOM_COUNT,
          primaryKey: 'createDate',
          primaryKeyValue: orderOV.createDate!.millisecondsSinceEpoch);
    } else {
      // 记录不存在，执行插入操作
      await insertData(temp.toMap(), TableName.ROOM_COUNT);
    }
  }

  ///汇总页面,根据目标 年月 获取汇总数据
  Future<ReviewVO> getReviewByYearAndMonth(int year, int month) async {
    Database db = await instance.database;
    List<Map<String, dynamic>> queryMaps = await db.rawQuery('''
        select 
        t1.createDateYear,t1.createDateMonth,t2.fulRentRate,
        sum(t1.rent) as rent ,
        sum(t1.otherCharge) as otherCharge,
        sum(t1.electricityAmountResult)as eleAmount,
        sum(t1.waterAmountResult)as waterAmount,
        sum(t1.electricityCharge)as eleCharge,
        sum (t1.waterCharge)as waterCharge 
        from ${TableName.ORDER} t1 
        left join ${TableName.ROOM_COUNT} t2 on t1.createDateYear = t2.createDateYear and t1.createDateMonth = t2.createDateMonth
        where  t1.createDateYear = ? 
        and t1.createDateMonth = ?
        and t1.state = ${OrderState.BILL_OUT}
        group by t1.createDateMonth
    
    ''', [year, month]);

    List<ReviewVO> result = [];
    for (var map in queryMaps) {
      result.add(ReviewVO.fromMap(map));
    }
    if (result.isEmpty) {
      return ReviewVO.onlyDate(year, month);
    }
    return result[0];
  }

  ///汇总页面,根据目标 年 获取每月数据(补全12条记录)
  Future<List<ReviewVO>> getReviewByYearFilled(int year, bool fillUp) async {
    Database db = await instance.database;
    List<Map<String, dynamic>> queryMaps = await db.rawQuery('''
        select 
        t1.createDateYear,t1.createDateMonth,t2.fulRentRate,
        sum(t1.rent) as rent ,
        sum(t1.otherCharge) as otherCharge,
        sum(t1.electricityAmountResult)as eleAmount,
        sum(t1.waterAmountResult)as waterAmount,
        sum(t1.electricityCharge)as eleCharge,
        sum (t1.waterCharge)as waterCharge 
        from ${TableName.ORDER} t1
        left join 
        (select createDateYear,avg(fulRentRate)as fulRentRate from ${TableName
        .ROOM_COUNT} group by createDateYear)
        t2 on t1.createDateYear =t2.createDateYear
        where  t1.createDateYear = ? and  t1.state = ${OrderState.BILL_OUT}
        group by t1.createDateYear,t1.createDateMonth
        order by t1.createDateMonth asc
    
    ''', [year]);

    List<ReviewVO> result = [];
    for (var map in queryMaps) {
      result.add(ReviewVO.fromMap(map));
    }

    //补全十二条记录,用于画图
    if (fillUp) {
      if (result.length < 12) {
        List<ReviewVO> resultFilled = [];
        //由于记录并非按顺序,所以需要从头开始遍历补全
        A:
        for (int i = 1; i <= 12; i++) {
          for (ReviewVO temp in result) {
            //检测是否存在结果,存在则保存
            if (temp.createDateMonth == i) {
              resultFilled.add(temp);
              continue A;
            }
          }
          //不存在,添加空结果
          resultFilled.add(ReviewVO.onlyDate(year, i));
        }
        return resultFilled;
      }
    }

    return result;
  }

  ///汇总页面,累计进账
  Future<double> getReviewSumIncome() async {
    Database db = await instance.database;
    List<Map<String, dynamic>> queryMaps = await db.rawQuery('''
        select 
        (
        sum(t1.rent) +
        sum(t1.otherCharge) +
        sum(t1.electricityCharge)  +
        sum (t1.waterCharge)  
        ) as rent
        from ${TableName.ORDER} t1 
        where t1.state = ${OrderState.BILL_OUT}
    ''');
    List<ReviewVO> result = [];
    for (var map in queryMaps) {
      result.add(ReviewVO.fromMap(map));
    }
    return result[0].rent ?? 0;
  }

  ///汇总页面,表格
  Future<List<ReviewVO>> getReviewTableData() async {
    Database db = await instance.database;
    List<Map<String, dynamic>> queryMaps = await db.rawQuery('''
        select 
        t1.createDateYear,t1.createDateMonth,t2.fulRentRate,
        sum(t1.rent) as rent ,
        sum(t1.otherCharge) as otherCharge,
        sum(t1.electricityAmountResult)as eleAmount,
        sum(t1.waterAmountResult)as waterAmount,
        sum(t1.electricityCharge)as eleCharge,
        sum (t1.waterCharge)as waterCharge 
        from ${TableName.ORDER} t1
        left join 
        (select createDateYear,avg(fulRentRate)as fulRentRate from ${TableName
        .ROOM_COUNT} group by createDateYear)
        t2 on t1.createDateYear =t2.createDateYear
        where t1.state = ${OrderState.BILL_OUT}
        group by t1.createDateYear
        order by t1.createDateYear desc
    
    ''');
    List<ReviewVO> result = [];
    for (var map in queryMaps) {
      result.add(ReviewVO.fromMap(map));
    }
    return result;
  }

  /***
   * 根据房号/日期,获取上一条记录的押金,若空则为0
   */
  Future<double> getLastCheckInDepositByRoomId_DateTime(int roomId,
      DateTime createDate) async {
    Database db = await instance.database;
    //获取创建日期小于目标值的数据.
    //此条件下,日期最大的一条数据,就是上月的数据
    //特殊情况:
    // 结果条数为0,暂时还不存在上月数据.此时返回0
    List<Map<String, dynamic>> queryMaps = await db.rawQuery('''
     select deposit from ${TableName.ROOM_CHECK_IN_OUT} 
     where roomId = ? and createDate<? 
     order by createDate desc
     limit 1
    ''', [roomId, createDate.millisecondsSinceEpoch]);

    List<RoomCheckInOut> result = [];
    for (var map in queryMaps) {
      result.add(RoomCheckInOut.fromMap(map));
    }
    if (result.isEmpty) {
      //结果条数为0,暂时还不存在上月数据.此时返回0
      return 0;
    } else {
      //正常情况,1条记录
      return result[0].deposit!;
    }
  }
}
